﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Web;

namespace NotebookWSA.Sales
{
    public class OrderManager:ConnectionManager
    {
        #region Others
        public bool IsDocumentExists(string _DOCUMENT_NUM)
        {
            DataSet ds = new DataSet();

            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ORDR WHERE DOCNUM = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", _DOCUMENT_NUM));
            SqlDataAdapter _ADAPTER = new SqlDataAdapter();

            _ADAPTER.SelectCommand = oCommand;
            _ADAPTER.Fill(ds);

            int iCount = ds.Tables[0].Rows.Count;

            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool IsCustomerReferenceExists(string CardCode, string Reference)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ORDR WHERE CardCode=@CardCode AND Reference=@Reference AND DocStatus <> 'Canceled'";
            oCommand.Parameters.Add(new SqlParameter("@CardCode", CardCode));
            oCommand.Parameters.Add(new SqlParameter("@Reference", Reference));

            oAdapter.SelectCommand = oCommand;

            DataTable dt = new DataTable();

            oAdapter.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                return true;
            }
            else { return false; }


        }


        public bool IsCustomerReferenceExists(string ExcludedSONumber, string CardCode, string Reference)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM ORDR WHERE CardCode=@CardCode AND Reference=@Reference AND DocStatus <> 'Canceled' AND DocNum NOT IN (@ExcludedSONum)";

            oCommand.Parameters.Add(new SqlParameter("@CardCode", CardCode));
            oCommand.Parameters.Add(new SqlParameter("@Reference", Reference));
            oCommand.Parameters.Add(new SqlParameter("@ExcludedSONum", ExcludedSONumber));

            oAdapter.SelectCommand = oCommand;

            DataTable dt = new DataTable();

            oAdapter.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                return true;
            }
            else { return false; }


        }

        #endregion

        #region Add/Update Order
        public SqlException AddOrder(OrderUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO ORDR (DocNum, DocStatus, PostingDate, DueDate, DocDate, Location, StoreCode, CardCode, CardName, ContactPerson, PaymentTerms, Reference, BillTo, ShipTo, SalesMan, Remarks, VATCode, VATRate, IsVATInclusive, GrossAmount, VATAmount, Discount1, Discount2, Discount3, Discount4, TotalDiscount, NetAmount, DateCreated, CreatedBy, DateModified, ModifiedBy) VALUES " +
                                                            " (@DocNum, @DocStatus, @PostingDate, @DueDate, @DocDate, @Location, @StoreCode, @CardCode, @CardName, @ContactPerson, @PaymentTerms, @Reference, @BillTo, @ShipTo, @SalesMan, @Remarks, @VATCode, @VATRate, @IsVATInclusive, @GrossAmount, @VATAmount, @Discount1, @Discount2, @Discount3, @Discount4, @TotalDiscount, @NetAmount, GetDate(), @CreatedBy, GetDate(), @ModifiedBy)";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
                oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

                oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
                oCommand.Parameters.Add(new SqlParameter("@StoreCode", oUnit.StoreCode));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
                oCommand.Parameters.Add(new SqlParameter("@PaymentTerms", oUnit.PaymentTerms));
                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
                oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
                oCommand.Parameters.Add(new SqlParameter("@SalesMan", oUnit.SalesMan));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@VATCode", oUnit.TaxCode));
                oCommand.Parameters.Add(new SqlParameter("@VATRate", oUnit.TaxRate));
                oCommand.Parameters.Add(new SqlParameter("@IsVATInclusive", oUnit.IsVATInclusive));

                oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));

                oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
                oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
                oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
                oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
                oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
                oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
                oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));



                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));




                oCommand.ExecuteNonQuery();

                if (oUnit.LineItems != null)
                {
                    foreach (DataRow oRow in oUnit.LineItems.Rows)
                    {
                        string _NewGUID = System.Guid.NewGuid().ToString();
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "INSERT INTO RDR1 (DocNum, ItemCode, ItemDesc, UoM, Qty, CQty, UnitPrice, PriceListCode, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuID) VALUES " +
                                                                        "(@DocNum, @ItemCode, @ItemDesc, @UoM, @Qty, @CQty, @UnitPrice, @PriceListCode, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuID)";

                        oCommand.Parameters.Add(new SqlParameter("@ItemGuID", _NewGUID));
                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@CQty", Convert.ToDouble(oRow["CQty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                        if (oRow["IsFree"] != DBNull.Value)
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                        }
                        else
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                        }
                        oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));

                        oCommand.ExecuteNonQuery();

                    }
                }

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException UpdateOrder(OrderUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE ORDR SET DocStatus=@DocStatus, PostingDate=@PostingDate, DueDate=@DueDate, DocDate=@DocDate, Location=@Location, StoreCode=@StoreCode, CardCode=@CardCode, CardName=@CardName, ContactPerson=@ContactPerson, PaymentTerms=@PaymentTerms, Reference=@Reference, BillTo=@BillTo, ShipTo=@ShipTo, SalesMan=@SalesMan, Remarks=@Remarks, GrossAmount=@GrossAmount, VATAmount=@VATAmount, Discount1=@Discount1, Discount2=@Discount2, Discount3=@Discount3, Discount4=@Discount4, TotalDiscount=@TotalDiscount, NetAmount=@NetAmount, DateModified=GetDate(), ModifiedBy=@ModifiedBy" +
                                                            " WHERE DocNum=@DocNum";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
                oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

                oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
                oCommand.Parameters.Add(new SqlParameter("@StoreCode", oUnit.StoreCode));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
                oCommand.Parameters.Add(new SqlParameter("@PaymentTerms", oUnit.PaymentTerms));

                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
                oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
                oCommand.Parameters.Add(new SqlParameter("@SalesMan", oUnit.SalesMan));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));
                oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
                oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
                oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
                oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
                oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
                oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
                oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));

                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();

                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE RDR1 WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.ExecuteNonQuery();

                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {

                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        string _NewGUID = System.Guid.NewGuid().ToString();
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "INSERT INTO RDR1 (DocNum, ItemCode, ItemDesc, UoM, Qty, CQty, PriceListCode, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                    "(@DocNum, @ItemCode, @ItemDesc, @UoM, @Qty, @CQty, @PriceListCode, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";


                        oCommand.Parameters.Add(new SqlParameter("@ItemGuid", _NewGUID));
                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                        if (oRow["Qty"] == DBNull.Value)
                        {
                            int _ZeroValue = 0;
                            oRow["Qty"] = _ZeroValue;
                        }
                        oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@CQty", Convert.ToDouble(oRow["CQty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                        if (oRow["IsFree"] != DBNull.Value)
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                        }
                        else
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                        }
                        oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));

                        oCommand.ExecuteNonQuery();

                    }

                }
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }
        #endregion

        #region Post/Cancel/Close Order
        public SqlException PostOrder(string sOrderNo)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE ORDR SET DOCSTATUS='Posted' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException CancelOrder(string sOrderNo)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE ORDR SET DOCSTATUS='Canceled' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
                oCommand.ExecuteNonQuery();
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException CloseOrder(string sOrderNo)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE ORDR SET DOCSTATUS='Closed' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
                oCommand.ExecuteNonQuery();
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException SetOrderOpen(string sOrderNo)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE ORDR SET DOCSTATUS='Open' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException SetOrderInProcess(string sOrderNo)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE ORDR SET DOCSTATUS='In-Process' WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sOrderNo));
                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        #endregion

        #region Getting Data

        public DataTable GetPostedOrderForPicking(DateTime _from, DateTime _to)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;
                oCommand.CommandText = "exec SP_PICKLIST @FROM, @TO";
                oCommand.Parameters.Add(new SqlParameter("@FROM", _from));
                oCommand.Parameters.Add(new SqlParameter("@TO", _to));

                DataTable dt = new DataTable();
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(dt);

                return dt;
            }
            catch (SqlException ex)
            {
                throw ex;
            }

        }

        public DataTable GetPostedOrder()
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM VW_SALESORDER where DOCSTATUS='Posted'";

                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public DataTable GetPostedOrder(string _DocumentNumber)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM VW_POSTED_SO WHERE DOCNUM = @DOCNUM";
                oCommand.Parameters.Add(new SqlParameter("@DOCNUM", _DocumentNumber));

                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetOrdersByCustomer(string _CardName)
        {
            try
            {
                _CardName = _CardName.Trim() + '%';
                SqlCommand oCommand = new SqlCommand();
                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT TOP 500 * FROM ORDR WHERE CardName LIKE @CardName ORDER BY DocNum DESC";
                oCommand.Parameters.Add(new SqlParameter("@CardName", _CardName));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetOrdersByCustomerCode(string _CARDCODE)
        {
            try
            {
                _CARDCODE = _CARDCODE.Trim() + '%';
                SqlCommand oCommand = new SqlCommand();
                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT TOP 500 * FROM ORDR WHERE CardCode LIKE @CardName ORDER BY DocNum DESC";
                oCommand.Parameters.Add(new SqlParameter("@CardName", _CARDCODE));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetOrdersByCustRefPO(string _CustRefPO)
        {
            try
            {
                _CustRefPO = _CustRefPO.Trim() + '%';
                SqlCommand oCommand = new SqlCommand();
                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT TOP 500 * FROM ORDR WHERE Reference LIKE @CustRefPO ORDER BY DocNum DESC";
                oCommand.Parameters.Add(new SqlParameter("@CustRefPO", _CustRefPO));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public DataTable GetOrders(string sDocumentNumber)
        {
            try
            {
                sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
                SqlCommand oCommand = new SqlCommand();
                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT TOP 500 * FROM ORDR WHERE DocNum LIKE @DOCNUM ORDER BY RecNo DESC";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public DataTable GetOrders(string _CardCode, string _DocNumber, string _DocumentStatus)
        {
            try
            {
                _DocNumber = '%' + _DocNumber.Trim() + '%';
                SqlCommand oCommand = new SqlCommand();
                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, DueDate, CardCode, CardName, NetAmount, DocStatus FROM ORDR WHERE CardCode=@CardCode AND DocNum LIKE @DOCNUM AND DocStatus=@DocStatus ORDER BY PostingDate DESC";
                oCommand.Parameters.Add(new SqlParameter("@CardCode", _CardCode));
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", _DocumentStatus));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public DataTable GetOpenAndPostedOrders(string _CardCode, string _DocNumber)
        {
            try
            {
                _DocNumber = '%' + _DocNumber.Trim() + '%';
                SqlCommand oCommand = new SqlCommand();
                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, DueDate, CardCode, CardName, NetAmount, DocStatus FROM ORDR WHERE CardCode=@CardCode AND DocNum LIKE @DOCNUM AND (DocStatus='Posted' Or DocStatus='Open') ORDER BY PostingDate DESC";
                oCommand.Parameters.Add(new SqlParameter("@CardCode", _CardCode));
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));


                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public string GetOrderNumber(string _Cardcode, string _DocNumber, string _DocumentStatus)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT DocNum FROM ORDR WHERE CardCode=@CardCode AND DocNum=@DOCNUM AND DocStatus=@DocStatus";
                oCommand.Parameters.Add(new SqlParameter("@CardCode", _Cardcode));
                oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", _DocumentStatus));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                if (ds.Tables[0].Rows.Count == 0)
                {
                    return "";
                }
                else
                {
                    DataRow oRow = ds.Tables[0].Rows[0];
                    return oRow["DocNum"].ToString();
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public DataRow GetOrder(string sDocumentNumber)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM ORDR WHERE DocNum = @DOCNUM";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                DataRow oRow = ds.Tables[0].Rows[0];

                return oRow;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public DataTable GetOrderLineItems(string sDocumentNumber)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM RDR1 WHERE DocNum = @DOCNUM ORDER BY RECINDEX";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Report Retreivable

        public DataTable GetOrderStatus(DateTime _FROM, DateTime _TO)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.StoredProcedure;
                oCommand.CommandText = "SP_SO_STATUS";
                oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
                oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public DataTable GetOrderStatusWithStatus(DateTime _FROM, DateTime _TO, bool _Draft, bool _Posted, bool _InProgress, bool _Open, bool _Closed)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.StoredProcedure;
                oCommand.CommandText = "SP_SO_STATUS_WITH_STATUS";
                oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
                oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

                oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT", Convert.ToInt16(_Draft)));
                oCommand.Parameters.Add(new SqlParameter("@WITHINPROCESS", Convert.ToInt16(_InProgress)));
                oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED", Convert.ToInt16(_Posted)));
                oCommand.Parameters.Add(new SqlParameter("@WITHOPEN", Convert.ToInt16(_Open)));
                oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED", Convert.ToInt16(_Closed)));
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public DataTable GetSalesOrder(DateTime _FROM, DateTime _TO, bool _Draft, bool _Posted, bool _InProgress, bool _Open, bool _Closed)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.StoredProcedure;
                oCommand.CommandText = "SP_LOGS_SO";
                oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
                oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

                oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT", Convert.ToInt16(_Draft)));
                oCommand.Parameters.Add(new SqlParameter("@INPROCESS", Convert.ToInt16(_InProgress)));
                oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED", Convert.ToInt16(_Posted)));
                oCommand.Parameters.Add(new SqlParameter("@WITHOPEN", Convert.ToInt16(_Open)));
                oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED", Convert.ToInt16(_Closed)));
                oCommand.Parameters.Add(new SqlParameter("@WITHAPPROVED", Convert.ToInt16(_Closed)));
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public DataTable GetSalesOrderPerItem(DateTime _FROM, DateTime _TO, bool _Draft, bool _Posted, bool _InProgress, bool _Open, bool _Closed)
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.StoredProcedure;
                oCommand.CommandText = "SP_LOGS_SO_PER_ITEM";
                oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
                oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

                oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT", Convert.ToInt16(_Draft)));
                oCommand.Parameters.Add(new SqlParameter("@INPROCESS", Convert.ToInt16(_InProgress)));
                oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED", Convert.ToInt16(_Posted)));
                oCommand.Parameters.Add(new SqlParameter("@WITHOPEN", Convert.ToInt16(_Open)));
                oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED", Convert.ToInt16(_Closed)));
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}